package org.sqlproc.engine;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.sqlproc.engine.impl.SqlMappingRule;
import org.sqlproc.engine.impl.SqlMetaStatement;
import org.sqlproc.engine.impl.SqlProcessResult;
import org.sqlproc.engine.impl.SqlUtils;

/**
 * The primary SQL Processor class for the META SQL query execution.
 * 
 * <p>
 * Instance of this class holds one META SQL query and one optional Mapping rule.
 * <p>
 * For example there's a table PERSON with two columns - ID and NAME. <br>
 * In queries.properties there's the next definition:
 * 
 * <pre>
 * QRY_ALL_PEOPLE= \
 *   select p.ID @id, p.NAME @name \
 *   from PERSON p \
 *   {= where \
 *    {& id=:id} \
 *    {& UPPER(name)=:+name} \
 *   } \
 *   {#1 order by ID} \
 *   {#2 order by NAME}
 * </pre>
 * 
 * <p>
 * In the case of SQL Processor initialization
 * 
 * <pre>
 * SqlPropertiesLoader loader = new SqlPropertiesLoader(&quot;queries.properties&quot;, this.getClass());
 * SqlEngineLoader sqlLoader = new SqlEngineLoader(loader.getProperties());
 * SqlQueryEngine sqlEngine = sqlLoader.getQueryEngine(&quot;ALL_PEOPLE&quot;);
 * </pre>
 * 
 * there's created an instance of SqlQueryEngine with the name <code>ALL_PEOPLE</code>.
 * 
 * <p>
 * Next the query can be executed with one of the <code>queryXXX</code> methods. For example there's a Java bean class
 * Person with attributes id and name. The invocation
 * 
 * <pre>
 * List&lt;Person&gt; list = sqlEngine.query(session, Person.class, null, SqlQueryEngine.ASC_ORDER);
 * </pre>
 * 
 * produces the next SQL execution
 * 
 * <pre>
 * select p.ID id, p.NAME name from PERSON p order by ID ASC
 * </pre>
 * 
 * <p>
 * Next there's an instance person of the class Person with the value <code>Jan</code> for the attribute name. The
 * invocation
 * 
 * <pre>
 * List&lt;Person&gt; list = sqlEngine.query(session, Person.class, person, SqlOrder.getDescOrder(2));
 * </pre>
 * 
 * produces the next SQL execution
 * 
 * <pre>
 * select p.ID id, p.NAME name from PERSON p where UPPER(name)=? order by NAME DESC
 * </pre>
 * 
 * <p>
 * For more info please see the Reference Guide or <a
 * href="http://code.google.com/p/sql-processor/w/list">tutorials</a>.
 * 
 * @author <a href="mailto:Vladimir.Hudec@gmail.com">Vladimir Hudec</a>
 */
public class SqlQueryEngine extends SqlEngine {

    /**
     * The ordering directive list with no ordering rule.
     */
    public static final SqlOrder NO_ORDER = SqlOrder.getOrder();

    /**
     * The ordering directive list with one asscending ordering rule.
     */
    public static final SqlOrder ASC_ORDER = SqlOrder.getAscOrder(1);

    /**
     * the ordering directive list with one descending ordering rule.
     */
    public static final SqlOrder DESC_ORDER = SqlOrder.getDescOrder(1);

    /**
     * Creates a new instance of SqlQueryEngine from one META SQL statement string and one SQL Mapping rule string.
     * Constructor will call the internal ANTLR parsers for the statement and the mapping instances construction. This
     * constructor is devoted to manual META SQL statements and Mapping rules construction. More obvious is to put these
     * definitions into queries.properties file and engage SqlEngineLoader for the SqlQueryEngine instances
     * construction.
     * 
     * @param name
     *            the name if this SQL Engine instance
     * @param statement
     *            the META SQL query statement, extension of ANSI SQL
     * @param mapping
     *            the SQL Mapping rule, SQL result to Java output classes mapping
     * @throws SqlEngineException
     *             mainly in the case the provided statements are not compliant with the ANTLR grammar
     */
    public SqlQueryEngine(String name, String statement, String mapping) throws SqlEngineException {

        super(name, SqlMetaStatement.getInstance(statement), (mapping != null) ? SqlMappingRule.getInstance(mapping)
                : null, null, null);
    }

    /**
     * Creates a new instance of SqlQueryEngine from one META SQL statement string and one SQL Mapping rule string.
     * Constructor will call the internal ANTLR parsers for the statement and the mapping instances construction.
     * Compared to the previous constructor, external SQL Monitor for the runtime statistics gathering is engaged. This
     * constructor is devoted to manual META SQL statements and Mapping rules construction. More obvious is to put these
     * statements into queries.properties file and engage SqlEngineLoader for instances construction.
     * 
     * @param name
     *            the name if this SQL Engine instance
     * @param statement
     *            the META SQL query statement, extension of ANSI SQL
     * @param mapping
     *            the SQL Mapping rule, SQL result to Java output classes mapping
     * @param monitor
     *            the SQL Monitor for the runtime statistics gathering
     * @param features
     *            the optional SQL Processor features
     * @throws SqlEngineException
     *             mainly in the case the provided statements are not compliant with the ANTLR grammar
     */
    public SqlQueryEngine(String name, String statement, String mapping, SqlMonitor monitor,
            Map<String, Object> features) throws SqlEngineException {

        super(name, SqlMetaStatement.getInstance(statement), (mapping != null) ? SqlMappingRule.getInstance(mapping)
                : null, monitor, features);
    }

    /**
     * Creates a new instance of SqlQueryEngine from one META SQL statement and one SQL Mapping rule instance. Both
     * parameters are already precompiled instances using the ANTLR parsers. This is the recommended usage for the
     * runtime performance optimization. This constructor is devoted to be used from the SqlEngineLoader, which is able
     * to read all definitions from an external queries.properties and create the named instances.
     * 
     * @param name
     *            the name if this SQL Engine instance
     * @param statement
     *            the precompiled META SQL query statement, extension of ANSI SQL
     * @param mapping
     *            the precompiled SQL Mapping rule, SQL result to Java output classes mapping
     */
    public SqlQueryEngine(String name, SqlMetaStatement statement, SqlMappingRule mapping) {

        super(name, statement, mapping, null, null);
    }

    /**
     * Creates a new instance of SqlQueryEngine from one META SQL statement and one SQL Mapping rule instance. Both
     * parameters are already precompiled instances using the ANTLR parsers. This is the recommended usage for the
     * runtime performance optimization. This constructor is devoted to be used from the SqlEngineLoader, which is able
     * to read all definitions from an external queries.properties and create the named instances. Compared to the
     * previous constructor, external SQL Monitor for the runtime statistics gathering is engaged.
     * 
     * @param name
     *            the name if this SQL Engine instance
     * @param statement
     *            the precompiled META SQL query statement, extension of ANSI SQL
     * @param mapping
     *            the precompiled SQL Mapping rule, SQL result to Java output classes mapping
     * @param monitor
     *            the SQL Monitor for the runtime statistics gathering
     * @param features
     *            the optional SQL Processor features
     */
    public SqlQueryEngine(String name, SqlMetaStatement statement, SqlMappingRule mapping, SqlMonitor monitor,
            Map<String, Object> features) {
        super(name, statement, mapping, monitor, features);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #query(Session, Class, Object, Object, SqlOrder, int, int, int)} .
     */
    public <E> List<E> query(Session session, Class<E> resultClass) throws HibernateException {
        return query(session, resultClass, new Object(), null, NO_ORDER, 0, 0, 0);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #query(Session, Class, Object, Object, SqlOrder, int, int, int)} .
     */
    public <E> List<E> query(Session session, Class<E> resultClass, Object dynamicInputValues)
            throws HibernateException {
        return query(session, resultClass, dynamicInputValues, null, NO_ORDER, 0, 0, 0);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #query(Session, Class, Object, Object, SqlOrder, int, int, int)} .
     */
    public <E> List<E> query(Session session, Class<E> resultClass, Object dynamicInputValues, SqlOrder order)
            throws HibernateException {
        return query(session, resultClass, dynamicInputValues, null, order, 0, 0, 0);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #query(Session, Class, Object, Object, SqlOrder, int, int, int)} .
     */
    public <E> List<E> query(Session session, Class<E> resultClass, Object dynamicInputValues, Object staticInputValues)
            throws HibernateException {
        return query(session, resultClass, dynamicInputValues, staticInputValues, NO_ORDER, 0, 0, 0);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #query(Session, Class, Object, Object, SqlOrder, int, int, int)} .
     */
    public <E> List<E> query(Session session, Class<E> resultClass, Object dynamicInputValues,
            Object staticInputValues, SqlOrder order) throws HibernateException {
        return query(session, resultClass, dynamicInputValues, staticInputValues, order, 0, 0, 0);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #query(Session, Class, Object, Object, SqlOrder, int, int, int)} .
     */
    public <E> List<E> query(Session session, Class<E> resultClass, Object dynamicInputValues, int firstResult,
            int maxResults) throws HibernateException {
        return query(session, resultClass, dynamicInputValues, null, NO_ORDER, 0, maxResults, firstResult);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #query(Session, Class, Object, Object, SqlOrder, int, int, int)} .
     */
    public <E> List<E> query(Session session, Class<E> resultClass, Object dynamicInputValues,
            Object staticInputValues, int firstResult, int maxResults) throws HibernateException {
        return query(session, resultClass, dynamicInputValues, staticInputValues, NO_ORDER, 0, maxResults, firstResult);
    }

    /**
     * Runs META SQL query statement obtain a list of database rows. This is the primary and the most complex SQL
     * Processor execution method to obtain a list of result class instances. Criteria to pickup the correct database
     * rows are taken from input values.
     * 
     * @param session
     *            Hibernate session, first level cache and the SQL query execution context
     * @param resultClass
     *            The class used for the return values, the SQL query execution output. This class is also named as the
     *            output class or the transport class, In fact it's a standard POJO class, which must include all the
     *            attributes described in the Mapping rule statement. This class itself and all its subclasses must have
     *            public constructors without any parameters. All the attributes used in the mapping rule statement must
     *            be accessible using public getters and setters. The instances of this class are created on the fly in
     *            the query execution using the reflection API.
     * @param dynamicInputValues
     *            The object used for the SQL statement dynamic parameters. The class of this object is also named as
     *            the input class or the dynamic parameters class. The exact class type isn't important, all the
     *            parameters substituted into the SQL prepared statement are picked up using the reflection API.
     * @param staticInputValues
     *            The object used for the SQL statement static parameters. The class of this object is also named as the
     *            input class or the static parameters class. The exact class type isn't important, all the parameters
     *            injected into the SQL query command are picked up using the reflection API. Compared to
     *            dynamicInputValues input parameters, parameters in this class should't be produced by the end user to
     *            prevent SQL injection threat!
     * @param order
     *            The ordering directive list. Using the class SqlOrder the ordering rules can be chained. Every
     *            ordering rule in this chain should correspond to one META SQL Ordering statement.
     * @param maxTimeout
     *            The max SQL execution time. This parameter can help to protect production system against ineffective
     *            SQL query commands. The value is in milliseconds.
     * @param maxResults
     *            The max number of SQL execution output rows, which can be returned as the result list. The primary
     *            usage is to support the pagination.
     * @param firstResult
     *            The first SQL execution output row to be returned in the case we need to skip some rows in the result
     *            set. The primary usage is to support the pagination.
     * @return The list of the resultClass instances.
     * @throws org.hibernate.HibernateException
     */
    @SuppressWarnings("unchecked")
    public <E> List<E> query(final Session session, final Class<E> resultClass, final Object dynamicInputValues,
            final Object staticInputValues, final SqlOrder order, final int maxTimeout, final int maxResults,
            final int firstResult) throws HibernateException {
        if (logger.isDebugEnabled()) {
            logger.debug(">> query, session=" + session + ", resultClass=" + resultClass + ", dynamicInputValues="
                    + dynamicInputValues + ", staticInputValues=" + staticInputValues + ", order=" + order
                    + ", maxTimeout=" + maxTimeout + ", maxResults=" + maxResults + ", firstResult=" + firstResult);
        }

        List<E> result = null;

        try {
            result = monitor.runList(new SqlMonitor.Runner() {
                public List<E> run() {
                    SqlProcessResult processResult = statement.process(SqlMetaStatement.Type.QUERY, dynamicInputValues,
                            staticInputValues, order.getOrders(), features);
                    SQLQuery query = session.createSQLQuery(processResult.getSql().toString());
                    if (maxTimeout > 0)
                        query.setTimeout(maxTimeout);
                    processResult.setQueryParams(session, query);
                    SqlMappingRule resultMapping = SqlMappingRule.merge(mapping, processResult);
                    resultMapping.setQueryResultMapping(resultClass, query);

                    if (firstResult > 0) {
                        query.setFirstResult(firstResult);
                        query.setMaxResults(maxResults);
                    } else if (maxResults > 0) {
                        query.setMaxResults(maxResults + 1);
                    }

                    List list = query.list();
                    List<E> result = new ArrayList<E>();
                    E resultInstance;
                    for (Iterator i$ = list.iterator(); i$.hasNext();) {
                        Object resultRow = i$.next();
                        Object resultArray[] = (resultRow instanceof Object[]) ? (Object[]) (Object[]) resultRow
                                : (new Object[] { resultRow });
                        resultInstance = (E) SqlUtils.getInstance(resultClass);
                        resultMapping.setQueryResultData(resultInstance, resultArray);
                        result.add(resultInstance);
                    }
                    return result;
                }
            }, resultClass);
            return result;
        } finally {
            if (logger.isDebugEnabled()) {
                logger.debug("<< query, result=" + result);
            }
        }
    }

    /**
     * Runs META SQL query statement obtain the number of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #queryCount(Session, Object, Object, SqlOrder, int)} .
     */
    public int queryCount(Session session) throws HibernateException {
        return queryCount(session, new Object(), null, NO_ORDER, 0);
    }

    /**
     * Runs META SQL query statement obtain the number of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #queryCount(Session, Object, Object, SqlOrder, int)} .
     */
    public int queryCount(Session session, Object dynamicInputValues) throws HibernateException {
        return queryCount(session, dynamicInputValues, null, NO_ORDER, 0);
    }

    /**
     * Runs META SQL query statement obtain the number of database rows. This is one of the overriden methods. For the
     * parameters description please see the most complex execution method
     * {@link #queryCount(Session, Object, Object, SqlOrder, int)} .
     */
    public int queryCount(Session session, Object dynamicInputValues, Object staticInputValues)
            throws HibernateException {
        return queryCount(session, dynamicInputValues, staticInputValues, NO_ORDER, 0);
    }

    /**
     * Runs META SQL query statement obtain the number of database rows. This is the primary and the most complex SQL
     * Processor execution method to count the rows in database, which match the criteria. These criteria are taken from
     * input values. The primary usage is to support the pagination.
     * 
     * @param session
     *            Hibernate session, first level cache and the SQL query execution context
     * @param dynamicInputValues
     *            The object used for the SQL statement dynamic parameters. The class of this object is also named as
     *            the input class or the dynamic parameters class. The exact class type isn't important, all the
     *            parameters substituted into the SQL prepared statement are picked up using the reflection API.
     * @param staticInputValues
     *            The object used for the SQL statement static parameters. The class of this object is also named as the
     *            input class or the static parameters class. The exact class type isn't important, all the parameters
     *            injected into the SQL query command are picked up using the reflection API. Compared to
     *            dynamicInputValues input parameters, parameters in this class should't be produced by the end user to
     *            prevent SQL injection threat!
     * @param order
     *            The ordering directive list. Using the class SqlOrder the ordering rules can be chained. Every
     *            ordering rule in this chain should correspond to one META SQL Ordering statement.
     * @param maxTimeout
     *            The max SQL execution time. This parameter can help to protect production system against ineffective
     *            SQL query commands. The value is in milliseconds.
     * @return The size of potential list of resultClass instances.
     * @throws org.hibernate.HibernateException
     */
    public int queryCount(final Session session, final Object dynamicInputValues, final Object staticInputValues,
            final SqlOrder order, final int maxTimeout) throws HibernateException {
        if (logger.isDebugEnabled()) {
            logger.debug(">> queryCount, session=" + session + ", dynamicInputValues=" + dynamicInputValues
                    + ", staticInputValues=" + staticInputValues + ", order=" + order + ", maxTimeout=" + maxTimeout);
        }

        Integer count = null;

        try {
            count = monitor.run(new SqlMonitor.Runner() {
                public Integer run() {
                    SqlProcessResult processResult = statement.process(SqlMetaStatement.Type.QUERY, dynamicInputValues,
                            staticInputValues, order.getOrders(), features);
                    SQLQuery query = session.createSQLQuery(processResult.getSql().toString());
                    if (maxTimeout > 0)
                        query.setTimeout(maxTimeout);
                    processResult.setQueryParams(session, query);

                    SQLQuery queryCount = session.createSQLQuery(countSql(processResult));
                    queryCount.addScalar("vysledek", Hibernate.INTEGER);
                    if (maxTimeout > 0)
                        queryCount.setTimeout(maxTimeout);
                    processResult.setQueryParams(session, queryCount);
                    return (Integer) queryCount.uniqueResult();
                }
            }, Integer.class);
            return count;
        } finally {
            if (logger.isDebugEnabled()) {
                logger.debug("<< queryCount, count=" + count);
            }
        }
    }

    // TODO - lepsi optimalizaci na dotaz - upravou puvodniho SQL dotazu

    private String countSql(SqlProcessResult processResult) {
        String s = processResult.getSql().toString().toUpperCase();
        int start = s.indexOf("ID");
        int end = s.indexOf("FROM");
        StringBuilder sb = processResult.getSql();
        if (start < 0 || end < 0 || start > end)
            return "select count(*) as vysledek from (" + sb.toString() + ") derived";
        String s1 = sb.substring(0, start + 2);
        String s2 = sb.substring(end);
        start = s1.toUpperCase().indexOf("SELECT");
        if (start < 0)
            return "select count(*) as vysledek from (" + sb.toString() + ") derived";
        return s1.substring(0, start) + "select count(" + s1.substring(start + 6) + ") as vysledek " + s2;
    }

    /**
     * Because the SQL Processor is Data Driven Query engine, every input parameters can produce in fact different SQL
     * query command. This method can help to identify the exact SQL query command, which is produced in the background
     * of the SQL Processor execution. The query is derived from the META SQL statement.
     * 
     * @param dynamicInputValues
     *            The object used for the SQL statement dynamic parameters. The class of this object is also named as
     *            the input class or the dynamic parameters class. The exact class type isn't important, all the
     *            parameters substituted into the SQL prepared statement are picked up using the reflection API.
     * @param staticInputValues
     *            The object used for the SQL statement static parameters. The class of this object is also named as the
     *            input class or the static parameters class. The exact class type isn't important, all the parameters
     *            injected into the SQL query command are picked up using the reflection API. Compared to
     *            dynamicInputValues input parameters, parameters in this class should't be produced by the end user to
     *            prevent SQL injection threat!
     * @param order
     *            The ordering directive list. Using the class SqlOrder the ordering rules can be chained. Every
     *            ordering rule in this chain should correspond to one META SQL Ordering statement.
     * @return The SQL query command derived from the META SQL statement based in the input parameters.
     * @throws org.hibernate.HibernateException
     */
    public String getSql(final Object dynamicInputValues, final Object staticInputValues, final SqlOrder order)
            throws HibernateException {
        if (logger.isDebugEnabled()) {
            logger.debug(">> getSql, dynamicInputValues=" + dynamicInputValues + ", staticInputValues="
                    + staticInputValues + ", order=" + order);
        }

        String sql = null;

        try {
            sql = monitor.run(new SqlMonitor.Runner() {

                public String run() {
                    SqlProcessResult processResult = statement.process(SqlMetaStatement.Type.QUERY, dynamicInputValues,
                            staticInputValues, order.getOrders(), features);
                    return processResult.getSql().toString();
                }
            }, String.class);
            return sql;
        } finally {
            if (logger.isDebugEnabled()) {
                logger.debug("<< getSql, sql=" + sql);
            }
        }
    }

    /**
     * Returns the name of this META SQL, which uniquely identifies the instance. In the case the META SQL statement and
     * Mapping rule are located in queries.properties, this name is the unique part of the keys in this file. For
     * example for the name ALL in queries.properties there's META SQL statement with the name QRY_ALL and Mapping rule
     * with the name OUT_ALL.
     * 
     * @return The name of the SQL engine instance.
     */
    public String getName() {
        return name;
    }

    /**
     * Returns the SQL Monitor instance for the runtime statistics gathering. By default no runtime statistics gathering
     * is active. So this SQL Monitor is implied in SQL engine constructor in the case the statistics gathering should
     * be engaged.
     * 
     * @return The SQL Monitor instance, which is active for this SQL engine instance.
     */
    public SqlMonitor getMonitor() {
        return monitor;
    }
}
